package com.sit.estatemanagementserver.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

// 重构: 所有建表语句已根据新的实体类进行调整。
@Repository
public class DatabaseManager {
    public static final String TAG = "DatabaseManager";
    private final DataSource dataSource;

    @Autowired
    public DatabaseManager(DataSource dataSource) {
        this.dataSource = dataSource;
        initializeDatabase();
    }

    // SQL CREATE TABLE 语句
    private static final String SQL_CREATE_OWNERS_TABLE =
            "CREATE TABLE IF NOT EXISTS " + DatabaseContract.OwnerEntry.TABLE_NAME + " (" +
                    DatabaseContract.OwnerEntry.COLUMN_PHONE + " TEXT PRIMARY KEY," +
                    DatabaseContract.OwnerEntry.COLUMN_PASSWORD + " TEXT NOT NULL," +
                    DatabaseContract.OwnerEntry.COLUMN_USERNAME + " TEXT NOT NULL);";

    private static final String SQL_CREATE_ROOMS_TABLE =
            "CREATE TABLE IF NOT EXISTS " + DatabaseContract.RoomEntry.TABLE_NAME + " (" +
                    DatabaseContract.RoomEntry.COLUMN_ROOM_NUMBER + " TEXT PRIMARY KEY," +
                    DatabaseContract.RoomEntry.COLUMN_WATER_BALANCE + " REAL DEFAULT 0," +
                    DatabaseContract.RoomEntry.COLUMN_ELECTRICITY_BALANCE + " REAL DEFAULT 0," +
                    DatabaseContract.RoomEntry.COLUMN_PROPERTY_FEE_BALANCE + " REAL DEFAULT 0," +
                    DatabaseContract.RoomEntry.COLUMN_OWNER_PHONE_FK + " TEXT," +
                    "FOREIGN KEY(" + DatabaseContract.RoomEntry.COLUMN_OWNER_PHONE_FK + ") REFERENCES " +
                    DatabaseContract.OwnerEntry.TABLE_NAME + "(" + DatabaseContract.OwnerEntry.COLUMN_PHONE + ") ON DELETE SET NULL ON UPDATE CASCADE);";

    private static final String SQL_CREATE_IMAGES_TABLE =
            "CREATE TABLE IF NOT EXISTS " + DatabaseContract.ImageEntry.TABLE_NAME + " (" +
                    DatabaseContract.ImageEntry.COLUMN_IMAGE_ID + " TEXT PRIMARY KEY," +
                    DatabaseContract.ImageEntry.COLUMN_IMAGE_PATH + " TEXT NOT NULL);";

    private static final String SQL_CREATE_REPAIRS_TABLE =
            "CREATE TABLE IF NOT EXISTS " + DatabaseContract.RepairEntry.TABLE_NAME + " (" +
                    DatabaseContract.RepairEntry.COLUMN_WORK_ORDER_ID + " TEXT PRIMARY KEY," +
                    DatabaseContract.RepairEntry.COLUMN_MESSAGE + " TEXT," +
                    DatabaseContract.RepairEntry.COLUMN_IMAGE_ID_FK + " TEXT," +
                    DatabaseContract.RepairEntry.COLUMN_IS_HANDLED + " INTEGER DEFAULT 0," +
                    DatabaseContract.RepairEntry.COLUMN_ROOM_NUMBER_FK + " TEXT," +
                    "FOREIGN KEY(" + DatabaseContract.RepairEntry.COLUMN_ROOM_NUMBER_FK + ") REFERENCES " +
                    DatabaseContract.RoomEntry.TABLE_NAME + "(" + DatabaseContract.RoomEntry.COLUMN_ROOM_NUMBER + ") ON DELETE CASCADE ON UPDATE CASCADE," +
                    "FOREIGN KEY(" + DatabaseContract.RepairEntry.COLUMN_IMAGE_ID_FK + ") REFERENCES " +
                    DatabaseContract.ImageEntry.TABLE_NAME + "(" + DatabaseContract.ImageEntry.COLUMN_IMAGE_ID + ") ON DELETE SET NULL ON UPDATE CASCADE);";

    private static final String SQL_CREATE_COMPLAINTS_TABLE =
            "CREATE TABLE IF NOT EXISTS " + DatabaseContract.ComplaintEntry.TABLE_NAME + " (" +
                    DatabaseContract.ComplaintEntry.COLUMN_WORK_ORDER_ID + " TEXT PRIMARY KEY," +
                    DatabaseContract.ComplaintEntry.COLUMN_MESSAGE + " TEXT," +
                    DatabaseContract.ComplaintEntry.COLUMN_IMAGE_ID_FK + " TEXT," +
                    DatabaseContract.ComplaintEntry.COLUMN_OWNER_PHONE_FK + " TEXT," +
                    "FOREIGN KEY(" + DatabaseContract.ComplaintEntry.COLUMN_OWNER_PHONE_FK + ") REFERENCES " +
                    DatabaseContract.OwnerEntry.TABLE_NAME + "(" + DatabaseContract.OwnerEntry.COLUMN_PHONE + ") ON DELETE CASCADE ON UPDATE CASCADE," +
                    "FOREIGN KEY(" + DatabaseContract.ComplaintEntry.COLUMN_IMAGE_ID_FK + ") REFERENCES " +
                    DatabaseContract.ImageEntry.TABLE_NAME + "(" + DatabaseContract.ImageEntry.COLUMN_IMAGE_ID + ") ON DELETE SET NULL ON UPDATE CASCADE);";

    private static final String SQL_CREATE_FACILITIES_TABLE =
            "CREATE TABLE IF NOT EXISTS " + DatabaseContract.FacilityEntry.TABLE_NAME + " (" +
                    DatabaseContract.FacilityEntry.COLUMN_FACILITY_ID + " TEXT PRIMARY KEY," +
                    DatabaseContract.FacilityEntry.COLUMN_FACILITY_NAME + " TEXT NOT NULL);";

    private static final String SQL_CREATE_FACILITY_RENTALS_TABLE =
            "CREATE TABLE IF NOT EXISTS " + DatabaseContract.FacilityRentalEntry.TABLE_NAME + " (" +
                    DatabaseContract.FacilityRentalEntry.COLUMN_WORK_ORDER_ID + " TEXT PRIMARY KEY," +
                    DatabaseContract.FacilityRentalEntry.COLUMN_FACILITY_ID_FK + " TEXT NOT NULL," +
                    DatabaseContract.FacilityRentalEntry.COLUMN_OWNER_PHONE_FK + " TEXT NOT NULL," +
                    DatabaseContract.FacilityRentalEntry.COLUMN_RENTAL_INFO + " TEXT," +
                    DatabaseContract.FacilityRentalEntry.COLUMN_START_TIME + " TEXT NOT NULL," +
                    DatabaseContract.FacilityRentalEntry.COLUMN_END_TIME + " TEXT NOT NULL," +
                    "FOREIGN KEY(" + DatabaseContract.FacilityRentalEntry.COLUMN_FACILITY_ID_FK + ") REFERENCES " +
                    DatabaseContract.FacilityEntry.TABLE_NAME + "(" + DatabaseContract.FacilityEntry.COLUMN_FACILITY_ID + ") ON DELETE RESTRICT ON UPDATE CASCADE," +
                    "FOREIGN KEY(" + DatabaseContract.FacilityRentalEntry.COLUMN_OWNER_PHONE_FK + ") REFERENCES " +
                    DatabaseContract.OwnerEntry.TABLE_NAME + "(" + DatabaseContract.OwnerEntry.COLUMN_PHONE + ") ON DELETE CASCADE ON UPDATE CASCADE);";

    private static final String SQL_CREATE_VISIT_INFO_TABLE =
            "CREATE TABLE IF NOT EXISTS " + DatabaseContract.VisitInfoEntry.TABLE_NAME + " (" +
                    DatabaseContract.VisitInfoEntry.COLUMN_VISITOR_ID + " TEXT PRIMARY KEY," +
                    DatabaseContract.VisitInfoEntry.COLUMN_VISITOR_NAME + " TEXT NOT NULL," +
                    DatabaseContract.VisitInfoEntry.COLUMN_VISITOR_PHONE + " TEXT," +
                    DatabaseContract.VisitInfoEntry.COLUMN_ROOM_NUMBER_FK + " TEXT NOT NULL," +
                    DatabaseContract.VisitInfoEntry.COLUMN_VISIT_PURPOSE + " TEXT," +
                    DatabaseContract.VisitInfoEntry.COLUMN_VISIT_START_TIME + " TEXT NOT NULL," +
                    DatabaseContract.VisitInfoEntry.COLUMN_VISIT_END_TIME + " TEXT NOT NULL," +
                    // DatabaseContract.VisitInfoEntry.COLUMN_QR_CODE_ID_FK + " TEXT," + // 删除或注释掉此行
                    DatabaseContract.VisitInfoEntry.COLUMN_QR_PAYLOAD + " TEXT," + // 新增此行
                    "FOREIGN KEY(" + DatabaseContract.VisitInfoEntry.COLUMN_ROOM_NUMBER_FK + ") REFERENCES " +
                    DatabaseContract.RoomEntry.TABLE_NAME + "(" + DatabaseContract.RoomEntry.COLUMN_ROOM_NUMBER + ") ON DELETE CASCADE ON UPDATE CASCADE" +
                    // 删除关于qr_code_id_fk的外键约束
                    ");";

    public Connection connect() throws SQLException {
        Connection conn = dataSource.getConnection();
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("PRAGMA foreign_keys = ON;");
        }
        return conn;
    }

    private void initializeDatabase() {
        try (Connection conn = connect(); Statement stmt = conn.createStatement()) {
            System.out.println(TAG + ": Initializing database schema...");
            // 注意建表顺序，被引用的表（如owners, images）需要先创建
            stmt.execute(SQL_CREATE_OWNERS_TABLE);
            stmt.execute(SQL_CREATE_ROOMS_TABLE);
            stmt.execute(SQL_CREATE_IMAGES_TABLE);
            stmt.execute(SQL_CREATE_REPAIRS_TABLE);
            stmt.execute(SQL_CREATE_COMPLAINTS_TABLE);
            stmt.execute(SQL_CREATE_FACILITIES_TABLE);
            stmt.execute(SQL_CREATE_FACILITY_RENTALS_TABLE);
            stmt.execute(SQL_CREATE_VISIT_INFO_TABLE);
            System.out.println(TAG + ": Database schema initialized successfully.");
        } catch (SQLException e) {
            System.err.println(TAG + ": Error initializing database: " + e.getMessage());
            throw new RuntimeException("Failed to initialize database schema", e);
        }
    }
}